#-*- coding: UTF-8 -*-
import re
import os
import readtar
import devU
import toexcel
import utable
import mpath
import delquery

mpath.makepath()
list_s_workitem = list()
list_s_standard_list = list()
list_s_delivery_list = list()
list_s_delivery = list()
list_svn = {}
list_profiles = {}
#要解析的6张表
list_work = {}
list_workfs = {}
filename = input('输入文件名（包含路径）：\n')
names = readtar.untar(filename)
sqlfile = names[0]
fpath =  r'%s\%s'%(mpath.gettempath(),sqlfile)
print('读取中......')
with open(fpath, 'r', encoding='UTF-8',errors='ignore') as f:
	lines = f.readlines()
	for line in lines:
		temre1=re.compile(r'^INSERT\sINTO\s`s_standard_list`\sVALUES\s(.*);')
		match1=temre1.match(line)
		if match1:
			temp = utable.readsql(match1.group(1))
			list_s_standard_list += utable.getSStandardlist(temp)
		
		temre2=re.compile(r'^INSERT\sINTO\s`s_workitem`\sVALUES\s(.*);')
		match2=temre2.match(line)
		if match2:
			temp = utable.readsql(match2.group(1))
			list_s_workitem += utable.getSWorkitem(temp)
			
		temre3=re.compile(r'^INSERT\sINTO\s`s_delivery_list`\sVALUES\s(.*);')
		match3=temre3.match(line)
		if match3:
			temp = utable.readsql(match3.group(1))
			list_s_delivery_list += utable.getSDeliverylist(temp)
			
		temre4=re.compile(r'^INSERT\sINTO\s`s_delivery`\sVALUES\s(.*);')
		match4=temre4.match(line)
		if match4:
			temp = utable.readsql(match4.group(1))
			list_s_delivery += utable.getSDelivery(temp)
			
		temre5=re.compile(r'^INSERT\sINTO\s`s_svn_account`\sVALUES\s(.*);')
		match5=temre5.match(line)
		if match5:
			temp = utable.readsql(match5.group(1))
			list_svn.update(utable.svndict(temp)) 
		
		temre6=re.compile(r'^INSERT\sINTO\s`s_profiles`\sVALUES\s(.*);')
		match6=temre6.match(line)
		if match6:
			temp = utable.readsql(match6.group(1))
			list_profiles.update(utable.profilesdict(temp))

list_work = utable.getworkinfo(list_s_standard_list,list_s_workitem,list_svn)
list_workfs = utable.getworkinfofs(list_s_standard_list,list_s_workitem,list_svn)
		
ex_s_standard_list = utable.paSStandardlist(list_s_standard_list,list_s_workitem)
ex_s_delivery_list = utable.paSDeliverylist(list_s_delivery_list,list_s_delivery,list_s_workitem)

exporttype = input('输入操作类型(1-全量导出标准清单，2-按工作项导出标准清单，3-全量导出投放申请，4-按申请导出投放申请，其它-退出)：')
while exporttype in ['1','2','3','4']:

	if exporttype == '1':
		toexcel.getExcel('标准清单',ex_s_standard_list)
	elif exporttype == '2':
		if list_workfs:
			for key in list_workfs:
				print(list_workfs[key])
			workid = input('输入工作项GUID（不作输入则全部导出）：')
			if workid == '':
				for key in list_work:
					ex_byworkid_list = utable.pbSStandardlist(list_s_standard_list,list_s_workitem,key)
					toexcel.getExcel(list_work[key]+'标准清单',ex_byworkid_list)
			else:
				if workid in list_work:
					ex_byworkid_list = utable.pbSStandardlist(list_s_standard_list,list_s_workitem,workid)
					toexcel.getExcel(list_work[workid]+'标准清单',ex_byworkid_list)
				else:
					print("工作项GUID不存在")
		else:
			print("结果为空")
	elif exporttype == '3':
		ex_s_delivery_list_query = delquery.delselect(ex_s_delivery_list,list_profiles)
		toexcel.getExcel('投放申请',ex_s_delivery_list_query)
	elif exporttype == '4':
		ex_s_delivery_list_query = delquery.delselect(ex_s_delivery_list,list_profiles)
		delinfofs = utable.getdelinfofs(ex_s_delivery_list_query,list_svn)
		delinfo = utable.getdelinfo(ex_s_delivery_list_query,list_svn)
		if delinfofs:
			for key in delinfofs:
				print(delinfofs[key])
			workid = input('输入投放申请GUID（不作输入则全部导出）：')
			if workid == '':
				for key in delinfo:
					ex_byworkid_list = utable.pcSDeliverylist(ex_s_delivery_list_query,key)
					toexcel.getExcel(delinfo[key]+'投放申请',ex_byworkid_list)
			else:
				if workid in delinfo:
					ex_byworkid_list = utable.pcSDeliverylist(ex_s_delivery_list_query,workid)
					toexcel.getExcel(delinfo[workid]+'投放申请',ex_byworkid_list)
				else:
					print("投放申请GUID不存在")
		else:
			print("结果为空")

	exporttype = input('输入操作类型(1-全量导出标准清单，2-按工作项导出标准清单，3-全量导出投放申请，4-按申请导出投放申请，其它-退出)：')

print("程序结束")
os.system('pause')	

